﻿using OF.Lib.DataAccess.Core;
using OF.Lib.DataAccess.Entity;
using OF.Lib.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using static OF.Lib.DataAccess.SqlMapper;

namespace OF.Lib.DataAccess
{
    public partial class DAO
    {
        const string SPLITON_FIELD = "SysNo";
        const int DEFAULT_TIMEOUT = 30;

        #region 一、基于单个对象自动生成SQL脚本的简单CURD方法
        /// <summary>
        ///  单个对象(SignleObject）的数据插入，返回受影响行数。
        ///  对于单个对象，不用在sql config文件中提供脚本，DAO将自动创建SQL语句
        /// </summary>
        /// <typeparam name="T">泛型：对象类型</typeparam>
        /// <param name="dataParameters">数据实体：可以是T类型的实体，也可以是dynamic对象</param>
        /// <param name="excludeProperties">需要排除的属性，多个用半角逗号分隔，忽略大小写；通常需要排除自增量的主键属性</param>
        /// <param name="transaction">事务对象</param>
        /// <returns>返回受影响行数</returns>
        public static int SO_Insert<T>(object dataParameter, string excludeProperties, DDTM ddtm = null) where T : class, new()
        {
            Type type = typeof(T);
            string tName = type.Name;
            var soConfig = SOConfigHelper.GetSingleObjectConnKey(tName);
            DBGroup groupConfig = DBConfigHelper.GetDBGroup(soConfig.ConnKey);

            string sql = SqlBuilderFactory.BuildSOInsertSql<T>(groupConfig.DBType, soConfig.DBName, dataParameter, excludeProperties);
            int timeOut = DEFAULT_TIMEOUT;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(soConfig.ConnKey, true, out timeOut);
                return dbConn.Execute(sql, dataParameter, null, timeOut);
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(soConfig.ConnKey, true, out timeOut);
                return trans.Item1.Execute(sql, dataParameter, trans.Item2, timeOut);
            }

        }
        /// <summary>
        /// 单个对象(SignleObject）的数据插入，返回主键。
        /// 对于单个对象，不用在sql config文件中提供脚本，DAO将自动创建SQL语句
        /// </summary>
        /// <typeparam name="T">泛型：对象类型</typeparam>
        /// <typeparam name="PK">SingleObject对象主键的数据类型</typeparam>
        /// <param name="dataParameters">数据实体：可以是T类型的实体，也可以是dynamic对象</param>
        /// <param name="excludeProperties">需要排除的属性，多个用半角逗号分隔，忽略大小写；通常需要排除自增量的主键属性</param>
        /// <param name="transaction">事务对象</param>
        /// <returns>返回主键</returns>
        public static PK SO_Insert<T, PK>(object dataParameter, string excludeProperties, DDTM ddtm = null) where T : class, new()
        {
            Type type = typeof(T);
            string tName = type.Name;
            var soConfig = SOConfigHelper.GetSingleObjectConnKey(tName);
            DBGroup groupConfig = DBConfigHelper.GetDBGroup(soConfig.ConnKey);

            string sql = SqlBuilderFactory.BuildSOInsertSqlWithReturnPK<T>(groupConfig.DBType, soConfig.DBName, dataParameter, excludeProperties);
            int timeOut = DEFAULT_TIMEOUT;
 
            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(soConfig.ConnKey, true, out timeOut);
                return dbConn.ExecuteScalar<PK>(sql, dataParameter, null, timeOut);
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(soConfig.ConnKey, true, out timeOut);
                return trans.Item1.ExecuteScalar<PK>(sql, dataParameter, trans.Item2, timeOut);
            }


        }

        /// <summary>
        /// 单个对象(SignleObject）的数据加载。
        /// 对于单个对象，不用在sql config文件中提供脚本，DAO将自动创建SQL语句
        /// </summary>
        /// <typeparam name="T">泛型：对象类型</typeparam>
        /// <param name="whereMatch">基于SingleObject对象的拉姆达表达式，用来生成条件语句片段</param>
        /// <param name="isMasterDB">是否是加载Master DB的数据，默认是true；如果为fasle，则会从Salve DB中加载数据</param>
        /// <param name="transaction">事务对象</param>
        /// <returns>返回对象实体</returns>
        public static T SO_Load<T>(Expression<Func<T, bool>> whereMatch, bool isMasterDB = true, DDTM ddtm = null) where T : class, new()
        {

            Type type = typeof(T);
            string tName = type.Name;
            var soConfig = SOConfigHelper.GetSingleObjectConnKey(tName);
            DBGroup groupConfig = DBConfigHelper.GetDBGroup(soConfig.ConnKey);
            object paramObj = null;
            string sql = SqlBuilderFactory.BuildSOLoad<T>(groupConfig.DBType, soConfig.DBName, whereMatch, out paramObj);

            int timeOut = DEFAULT_TIMEOUT;
            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(soConfig.ConnKey, true, out timeOut);
                var lst = dbConn.Query<T>(sql, paramObj, null, true, timeOut).ToList<T>();

                return lst.FirstOrDefault();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(soConfig.ConnKey, true, out timeOut);
                var lst = trans.Item1.Query<T>(sql, paramObj, trans.Item2, true, timeOut).ToList<T>();
                return lst.FirstOrDefault();

            }

        }


        /// <summary>
        /// 单个对象(SignleObject）的数据列表获取。
        ///  对于单个对象，不用在sql config文件中提供脚本，DAO将自动创建SQL语句        /// </summary>
        /// <typeparam name="T">泛型：对象类型</typeparam>
        /// <param name="whereMatch">基于SingleObject对象的拉姆达表达式，用来生成条件语句片段</param>
        /// <param name="isMasterDB">是否是加载Master DB的数据，默认是true；如果为fasle，则会从Salve DB中加载数据</param>
        /// <param name="orderBy">排序要求</param>
        /// <param name="top">取前几条，为null表示不限制</param>
        /// <param name="transaction">事务对象</param>
        /// <returns>返回对象列表</returns>
        public static List<T> SO_GetList<T>(Expression<Func<T, bool>> whereMatch, bool isMasterDB = true, string orderBy = "", int? top = null,
            DDTM ddtm = null) where T : class, new()
        {
            Type type = typeof(T);
            string tName = type.Name;
            var soConfig = SOConfigHelper.GetSingleObjectConnKey(tName);
            DBGroup groupConfig = DBConfigHelper.GetDBGroup(soConfig.ConnKey);
            object paramObj = null;
            string sql = SqlBuilderFactory.BuildSOGetList<T>(groupConfig.DBType, top, soConfig.DBName, whereMatch, orderBy, out paramObj);

            int timeOut = DEFAULT_TIMEOUT;
            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(soConfig.ConnKey, isMasterDB, out timeOut);
                var lst = dbConn.Query<T>(sql, paramObj, null, true, timeOut).ToList<T>();
                return lst;
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(soConfig.ConnKey, true, out timeOut);
                var lst = trans.Item1.Query<T>(sql, paramObj, trans.Item2, true, timeOut).ToList<T>();
                return lst;

            }


        }

        /// <summary>
        /// 单个对象(SignleObject）的数据更新，返回受影响行数。
        /// 对于单个对象，不用在sql config文件中提供脚本，DAO将自动创建SQL语句
        /// </summary>
        /// <typeparam name="T">泛型：对象类型</typeparam>
        /// <param name="dataParameter">数据实体：可以是T类型的实体（该实体中所有对象属性-除了被排除的属性，都会被更新）；
        /// 也可以是dynamic对象（只会更新动态对象中声明了的属性，所以对于更新字段较少时，建议使用dynamic对象）。</param>
        /// <param name="excludeProperties">需要排除Update的属性，多个用半角逗号分隔，忽略大小写；通常需要排除自增量的主键属性、创建时间、创建人等</param>
        /// <param name="whereMatch">基于SingleObject对象的拉姆达表达式，用来生成条件语句片段</param>
        /// <param name="transaction">事务对象</param>
        /// <returns>返回受影响行数</returns>
        public static int SO_Update<T>(object dataParameter, string excludeProperties, Expression<Func<T, bool>> whereMatch, DDTM ddtm = null) where T : class, new()
        {
            Type type = typeof(T);
            string tName = type.Name;
            var soConfig = SOConfigHelper.GetSingleObjectConnKey(tName);
            DBGroup groupConfig = DBConfigHelper.GetDBGroup(soConfig.ConnKey);

            object paramObj = null;
            string sql = SqlBuilderFactory.BuildSOUpdateSql<T>(groupConfig.DBType, soConfig.DBName, dataParameter, excludeProperties, whereMatch, out paramObj);
            int timeOut = DEFAULT_TIMEOUT;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(soConfig.ConnKey, true, out timeOut);
                return dbConn.Execute(sql, paramObj, null, timeOut);
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(soConfig.ConnKey, true, out timeOut);
                return trans.Item1.Execute(sql, paramObj, trans.Item2, timeOut);
            }


        }

        /// <summary>
        /// 单个对象(SignleObject）的数据删除，返回受影响行数。对于单个对象，不用在sql config文件中提供脚本，DAO将自动创建SQL语句
        /// </summary>
        /// <typeparam name="T">泛型：对象类型</typeparam>
        /// <param name="whereMatch">基于SingleObject对象的拉姆达表达式，用来生成条件语句片段</param>
        public static int SO_Delete<T>(Expression<Func<T, bool>> whereMatch, DDTM ddtm = null) where T : class, new()
        {
            Type type = typeof(T);
            string tName = type.Name;
            var soConfig = SOConfigHelper.GetSingleObjectConnKey(tName);
            DBGroup groupConfig = DBConfigHelper.GetDBGroup(soConfig.ConnKey);
            object paramObj = null;
            string sql = SqlBuilderFactory.BuildSODelete<T>(groupConfig.DBType, soConfig.DBName, whereMatch, out paramObj);

            int timeOut = DEFAULT_TIMEOUT;
            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(soConfig.ConnKey, true, out timeOut);
                return dbConn.Execute(sql, paramObj, null, timeOut);
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(soConfig.ConnKey, true, out timeOut);
                return trans.Item1.Execute(sql, paramObj, trans.Item2, timeOut);
            }

        }
        #endregion

        #region 二、基于配置文件中Sql脚本的DAO处理
        #region 2.1 执行返回受影响行数
        /// <summary>
        /// 执行数据访问处理，返回受影响数
        /// </summary>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <returns>受影响数</returns>
        public static int Execute(string sqlKey, object param = null, DDTM ddtm = null)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                return dbConn.Execute(sqlStr, param, null, timeOut);
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> tuple  = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
 
                return tuple.Item1.Execute(sqlStr, param, tuple.Item2, timeOut);
            }

        }
        #endregion

        #region 2.2 执行返回首行首列值
        /// <summary>
        /// 执行数据访问处理，返回首行首列数据
        /// </summary>
        /// <typeparam name="T">首行首列数据类型</typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <returns>首行首列数据</returns>
        public static T ExecuteScalar<T>(string sqlKey, object param = null, DDTM ddtm = null)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                return dbConn.ExecuteScalar<T>(sqlStr, param, null, timeOut);
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                return trans.Item1.ExecuteScalar<T>(sqlStr, param, trans.Item2, timeOut);
            }


        }
        #endregion

        #region 2.3 执行返回主对象单个实体，支持到最多5个对象关联映射，关系为1-N
        /// <summary>
        /// 执行数据访问处理，返回直接对象实体
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        public static T ExecuteEntity<T>(string sqlKey, object param = null, DDTM ddtm = null)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                var lst = dbConn.Query<T>(sqlStr, param, null, true, timeOut);
                return lst.FirstOrDefault();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                var lst = trans.Item1.Query<T>(sqlStr, param, trans.Item2, true, timeOut);
                return lst.FirstOrDefault();
            }


        }

        /// <summary>
        /// 执行2个表关联查询的数据访问处理，返回主对象实体，支持1-N模式，即主对象为1，包含一个为对象或者对象列表的属性；
        /// 多个泛型对象关联时，泛型对象顺序请务必与SQL脚本中关联对象的顺序一致，以避免不同表中相同名称字段的处理混淆。
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="map">多表联查时，映射各对象之间关系，比如：(tfirst, tsecond) => { tfirst.RefBook = tsecond; tfirst.RefBookList.Add(tsecond); return tfirst; }</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <param name="splitOn">用于区分第二个表的开始字段(默认是:id)</param>
        /// <returns></returns>
        public static TReturn ExecuteEntity<TFirst, TSecond, TReturn>(string sqlKey, Func<TFirst, TSecond, TReturn> map, object param = null,
            DDTM ddtm = null, string splitOn = SPLITON_FIELD)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                return dbConn.Query<TFirst, TSecond, TReturn>(sqlStr, map, param, null, true, splitOn, timeOut).FirstOrDefault();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                return trans.Item1.Query<TFirst, TSecond, TReturn>(sqlStr, map, param, trans.Item2, true, splitOn, timeOut).FirstOrDefault();
            }

        }

        /// <summary>
        /// 执行3个表关联查询的数据访问处理，返回主对象实体，支持1-N-N模式，即主对象为1，包含2个为对象或者对象列表的属性；
        /// 多个泛型对象关联时，泛型对象顺序请务必与SQL脚本中关联对象的顺序一致，以避免不同表中相同名称字段的处理混淆。
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TThird"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="map">多表联查时，映射各对象之间关系，比如：(tfirst, tsecond) => { tfirst.RefBook = tsecond; tfirst.RefBookList.Add(tsecond); return tfirst; }</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <param name="splitOn">用于区分第二个表的开始字段(默认是:id)</param>
        /// <returns></returns>
        public static TReturn ExecuteEntity<TFirst, TSecond, TThird, TReturn>(string sqlKey, Func<TFirst, TSecond, TThird, TReturn> map, object param = null,
            DDTM ddtm = null, string splitOn = SPLITON_FIELD)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                return dbConn.Query<TFirst, TSecond, TThird, TReturn>(sqlStr, map, param, null, true, splitOn, timeOut).FirstOrDefault();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                return trans.Item1.Query<TFirst, TSecond, TThird, TReturn>(sqlStr, map, param, trans.Item2, true, splitOn, timeOut).FirstOrDefault();
            }


        }
        /// <summary>
        /// 执行4个表关联查询的数据访问处理，返回主对象实体，支持1-N-N-N模式，即主对象为1，包含3个为对象或者对象列表的属性；
        /// 多个泛型对象关联时，泛型对象顺序请务必与SQL脚本中关联对象的顺序一致，以避免不同表中相同名称字段的处理混淆。
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TThird"></typeparam>
        /// <typeparam name="TFourth"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="map">多表联查时，映射各对象之间关系，比如：(tfirst, tsecond) => { tfirst.RefBook = tsecond; tfirst.RefBookList.Add(tsecond); return tfirst; }</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <param name="splitOn">用于区分第二个表的开始字段(默认是:id)</param>
        /// <returns></returns>
        public static TReturn ExecuteEntity<TFirst, TSecond, TThird, TFourth, TReturn>(string sqlKey, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, object param = null,
            DDTM ddtm = null, string splitOn = SPLITON_FIELD)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                return dbConn.Query<TFirst, TSecond, TThird, TFourth, TReturn>(sqlStr, map, param, null, true, splitOn, timeOut).FirstOrDefault();
            }
            else
            {

                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                return trans.Item1.Query<TFirst, TSecond, TThird, TFourth, TReturn>(sqlStr, map, param, trans.Item2, true, splitOn, timeOut).FirstOrDefault();
            }
        }
        /// <summary>
        /// 执行5个表关联查询的数据访问处理，返回主对象实体，支持1-N-N-N-N模式，即主对象为1，包含4个为对象或者对象列表的属性；
        /// 多个泛型对象关联时，泛型对象顺序请务必与SQL脚本中关联对象的顺序一致，以避免不同表中相同名称字段的处理混淆。
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TThird"></typeparam>
        /// <typeparam name="TFourth"></typeparam>
        /// <typeparam name="TFifth"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="map">多表联查时，映射各对象之间关系，比如：(tfirst, tsecond) => { tfirst.RefBook = tsecond; tfirst.RefBookList.Add(tsecond); return tfirst; }</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <param name="splitOn">用于区分第二个表的开始字段(默认是:id)</param>
        /// <returns></returns>
        public static TReturn ExecuteEntity<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(string sqlKey, Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, object param = null,
           DDTM ddtm = null, string splitOn = SPLITON_FIELD)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                return dbConn.Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(sqlStr, map, param, null, true, splitOn, timeOut).FirstOrDefault();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                return trans.Item1.Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(sqlStr, map, param, trans.Item2, true, splitOn, timeOut).FirstOrDefault(); ;
            }

        }

        /// <summary>
        /// 执行6个表关联查询的数据访问处理，返回主对象实体，支持1-N-N-N-N-N模式，即主对象为1，包含5个为对象或者对象列表的属性；
        /// 多个泛型对象关联时，泛型对象顺序请务必与SQL脚本中关联对象的顺序一致，以避免不同表中相同名称字段的处理混淆。
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TThird"></typeparam>
        /// <typeparam name="TFourth"></typeparam>
        /// <typeparam name="TFifth"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="map">多表联查时，映射各对象之间关系，比如：(tfirst, tsecond) => { tfirst.RefBook = tsecond; tfirst.RefBookList.Add(tsecond); return tfirst; }</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <param name="splitOn">用于区分第二个表的开始字段(默认是:id)</param>
        /// <returns></returns>
        public static TReturn ExecuteEntity<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(string sqlKey, Func<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn> map, object param = null,
           DDTM ddtm = null, string splitOn = SPLITON_FIELD)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                return dbConn.Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(sqlStr, map, param, null, true, splitOn, timeOut).FirstOrDefault(); ;
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                return trans.Item1.Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(sqlStr, map, param, trans.Item2, true, splitOn, timeOut).FirstOrDefault(); ;
            }

        }
        #endregion

        #region 2.4 执行返回主对象列表，支持到最多5个对象关联映射，关系为1-N
        /// <summary>
        /// 执行数据访问处理，返回直接对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        public static List<T> ExecuteEntityList<T>(string sqlKey, object param = null, DDTM ddtm = null)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                var lst = dbConn.Query<T>(sqlStr, param, null, true, timeOut).ToList<T>();
                return lst.Distinct().ToList();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                var lst = trans.Item1.Query<T>(sqlStr, param, trans.Item2, true, timeOut).ToList<T>();
                return lst.Distinct().ToList();
            }


        }

        /// <summary>
        /// 执行2个表关联查询的数据访问处理，返回主对象列表，支持1-N模式，即主对象为1，包含1个为对象或者对象列表的属性；
        /// 多个泛型对象关联时，泛型对象顺序请务必与SQL脚本中关联对象的顺序一致，以避免不同表中相同名称字段的处理混淆。
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="map">多表联查时，映射各对象之间关系，比如：(tfirst, tsecond) => { tfirst.RefBook = tsecond; tfirst.RefBookList.Add(tsecond); return tfirst; }</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <param name="splitOn">用于区分第二个表的开始字段(默认是:id)</param>
        /// <returns></returns>
        public static List<TReturn> ExecuteEntityList<TFirst, TSecond, TReturn>(string sqlKey, Func<TFirst, TSecond, TReturn> map, object param = null,
            DDTM ddtm = null, string splitOn = SPLITON_FIELD)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                var lst = dbConn.Query<TFirst, TSecond, TReturn>(sqlStr, map, param, null, true, splitOn, timeOut).ToList<TReturn>(); ;
                return lst.Distinct().ToList();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                var lst = trans.Item1.Query<TFirst, TSecond, TReturn>(sqlStr, map, param, trans.Item2, true, splitOn, timeOut).ToList<TReturn>(); ;
                return lst.Distinct().ToList();
            }


        }
        /// <summary>
        /// 执行3个表关联查询的数据访问处理，返回主对象列表，支持1-N-N模式，即主对象为1，包含2个为对象或者对象列表的属性；
        /// 多个泛型对象关联时，泛型对象顺序请务必与SQL脚本中关联对象的顺序一致，以避免不同表中相同名称字段的处理混淆。
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TThird"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="map">多表联查时，映射各对象之间关系，比如：(tfirst, tsecond) => { tfirst.RefBook = tsecond; tfirst.RefBookList.Add(tsecond); return tfirst; }</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <param name="splitOn">用于区分第二个表的开始字段(默认是:id)</param>
        /// <returns></returns>
        public static List<TReturn> ExecuteEntityList<TFirst, TSecond, TThird, TReturn>(string sqlKey, Func<TFirst, TSecond, TThird, TReturn> map, object param = null,
           DDTM ddtm = null, string splitOn = SPLITON_FIELD)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;


            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                var lst = dbConn.Query<TFirst, TSecond, TThird, TReturn>(sqlStr, map, param, null, true, splitOn, timeOut).ToList<TReturn>(); ;
                return lst.Distinct().ToList();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                var lst = trans.Item1.Query<TFirst, TSecond, TThird, TReturn>(sqlStr, map, param, trans.Item2, true, splitOn, timeOut).ToList<TReturn>(); ;
                return lst.Distinct().ToList();
            }

        }
        /// <summary>
        /// 执行4个表关联查询的数据访问处理，返回主对象列表，支持1-N-N-N模式，即主对象为1，包含3个为对象或者对象列表的属性；
        /// 多个泛型对象关联时，泛型对象顺序请务必与SQL脚本中关联对象的顺序一致，以避免不同表中相同名称字段的处理混淆。
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TThird"></typeparam>
        /// <typeparam name="TFourth"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="map">多表联查时，映射各对象之间关系，比如：(tfirst, tsecond) => { tfirst.RefBook = tsecond; tfirst.RefBookList.Add(tsecond); return tfirst; }</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <param name="splitOn">用于区分第二个表的开始字段(默认是:id)</param>
        /// <returns></returns>
        public static List<TReturn> ExecuteEntityList<TFirst, TSecond, TThird, TFourth, TReturn>(string sqlKey, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, object param = null,
          DDTM ddtm = null, string splitOn = SPLITON_FIELD)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;
            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                var lst = dbConn.Query<TFirst, TSecond, TThird, TFourth, TReturn>(sqlStr, map, param, null, true, splitOn, timeOut).ToList<TReturn>(); ;
                return lst.Distinct().ToList();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                var lst = trans.Item1.Query<TFirst, TSecond, TThird, TFourth, TReturn>(sqlStr, map, param, trans.Item2, true, splitOn, timeOut).ToList<TReturn>(); ;
                return lst.Distinct().ToList();
            }

        }
        /// <summary>
        /// 执行5个表关联查询的数据访问处理，返回主对象列表，支持1-N-N-N-N模式，即主对象为1，包含4个为对象或者对象列表的属性；
        /// 多个泛型对象关联时，泛型对象顺序请务必与SQL脚本中关联对象的顺序一致，以避免不同表中相同名称字段的处理混淆。
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TThird"></typeparam>
        /// <typeparam name="TFourth"></typeparam>
        /// <typeparam name="TFifth"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="map">多表联查时，映射各对象之间关系，比如：(tfirst, tsecond) => { tfirst.RefBook = tsecond; tfirst.RefBookList.Add(tsecond); return tfirst; }</param>
        /// <param name="param">输入数据</param>
        /// <param name="ddtm">事务</param>
        /// <param name="splitOn">用于区分第二个表的开始字段(默认是:id)</param>
        /// <returns></returns>
        public static List<TReturn> ExecuteEntityList<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(string sqlKey, Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, object param = null,
         DDTM ddtm = null, string splitOn = SPLITON_FIELD)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                var lst = dbConn.Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(sqlStr, map, param, null, true, splitOn, timeOut).ToList<TReturn>(); ;
                return lst.Distinct().ToList();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                var lst = trans.Item1.Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(sqlStr, map, param, trans.Item2, true, splitOn, timeOut).ToList<TReturn>(); ;
                return lst.Distinct().ToList();
            }

        }

        /// <summary>
        /// 执行6个表关联查询的数据访问处理，返回主对象列表，支持1-N-N-N-N-N模式，即主对象为1，包含5个为对象或者对象列表的属性；
        /// 多个泛型对象关联时，泛型对象顺序请务必与SQL脚本中关联对象的顺序一致，以避免不同表中相同名称字段的处理混淆。
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TThird"></typeparam>
        /// <typeparam name="TFourth"></typeparam>
        /// <typeparam name="TFifth"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="map">多表联查时，映射各对象之间关系，比如：(tfirst, tsecond) => { tfirst.RefBook = tsecond; tfirst.RefBookList.Add(tsecond); return tfirst; }</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <param name="splitOn">用于区分第二个表的开始字段(默认是:id)</param>
        /// <returns></returns>
        public static List<TReturn> ExecuteEntityList<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(string sqlKey, Func<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn> map, object param = null,
         DDTM ddtm = null, string splitOn = SPLITON_FIELD)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                var lst = dbConn.Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(sqlStr, map, param, null, true, splitOn, timeOut).ToList<TReturn>(); ;
                return lst.Distinct().ToList();
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                var lst = trans.Item1.Query<TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TReturn>(sqlStr, map, param, trans.Item2, true, splitOn, timeOut).ToList<TReturn>(); ;
                return lst.Distinct().ToList();
            }


        }
        #endregion

        #region 2.5 执行多条SQL语句，按照先后顺序返回
        /// <summary>
        /// 执行多条SQL语句的数据访问处理，按照先后顺序返回
        /// </summary> 
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="param">输入数据</param>
        /// <param name="transaction">事务</param>
        /// <returns></returns>
        public static GridReader ExecuteMultiple(string sqlKey, object param = null, DDTM ddtm = null)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;


            IDbConnection dbConn;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
                return dbConn.QueryMultiple(sqlStr, param, null, timeOut);
            }
            else
            {
                Tuple<IDbConnection, IDbTransaction> trans = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                return trans.Item1.QueryMultiple(sqlStr, param, trans.Item2, timeOut);
            }

        }
        #endregion
        #endregion

        #region 三、基于配置文件中Sql脚本分页查询
        /// <summary>
        /// 基于配置文件中Sql脚本分页查询，必须采用分页查询模板语句，示例：
        /// SELECT r.SysNo,r.CustomerSysNo,r.BookSysNo,r.Title,r.InDate,c.Name 
        /// #FROM{[ TestDB.dbo.Review r 
        ///       INNER JOIN TestDB.dbo.Customer c 
        ///        ON r.CustomerSysNo = c.SysNo
        /// #WHERE{[ ( &lt;? c.LoginId=@LoginId  ?> or &lt;? c.name like @LoginId ?> ) and &lt;? r.indate>@start ?>  and  &lt;? c.SysNo in @SysNoes ?> ]}
        /// #SORT{[ Order By c.SysNo ]} 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlKey">SQL脚本Key</param>
        /// <param name="filter">分页查询条件对象</param>
        /// <returns></returns>
        public static QueryResult<T> PagedQuery<T>(string sqlKey, QueryFilter filter)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;
            IDbConnection dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);

            string sqlQuery = SqlBuilderFactory.BuildPagedQuerySql(sqlKey, filter);

            GridReader gr = dbConn.QueryMultiple(sqlQuery, filter, null, timeOut);
            QueryResult<T> result = new QueryResult<T>();
            result.PageIndex = filter.PageIndex;
            result.PageSize = filter.PageSize;
            result.TotalCount = gr.Read<int>(false).FirstOrDefault();
            result.Data = gr.Read<T>().ToList();
            return result;

        }

        #endregion

        /// <summary>
        /// 根据sqlKey，获取sql script语句和DbConnection，然后去自由处理
        /// </summary>
        /// <param name="sqlKey"></param>
        /// <param name="ddtm"></param>
        /// <returns>Tuple对象，Item1为SQL语句，Item2为连接对象，Item3为事务对象</returns>
        public static Tuple<string, IDbConnection, IDbTransaction> GetSqlAndConnection(string sqlKey, DDTM ddtm = null)
        {
            int timeOut = DEFAULT_TIMEOUT;
            string sqlStr = string.Empty;

            IDbConnection dbConn=null;
            IDbTransaction dbTrans=null;
            if (ddtm == null)
            {
                dbConn = DbConnFactory.GetDbConnection(sqlKey, out sqlStr, out timeOut);
            }
            else
            {
                if (ddtm == null)
                {
                    throw new Exception("Please input DDTM object for prameter 'IDbTransaction transaction'!");
                }
                Tuple<IDbConnection, IDbTransaction> tuple = ddtm.GetDbConnectionAndTransaction(sqlKey, out sqlStr, out timeOut);
                dbConn = tuple.Item1;
                dbTrans = tuple.Item2;
            }
            Tuple<string, IDbConnection, IDbTransaction> tuple3 = new Tuple<string, IDbConnection, IDbTransaction>(sqlStr, dbConn, dbTrans);
            return tuple3;


        }

    }
}
